﻿using System;
using System.Text;
using System.Data;
using CRMFramework;
using System.Text.RegularExpressions;
using System.Globalization;

namespace CRMBiz
{
    public static class ApplicationContactHandler
    {
        public static DataSet GetApplicationContactList(int pageIndex, int pageSize, string searchtext, string conditiontext, string sortingText, string days)
        {
            DataSet ds;
            try
            {
                string strdays = string.Empty;
                if (days == "30")
                    strdays = " AND DATEDIFF(now(), EditDate) < 30";
                else if (days == "90")
                    strdays = " AND DATEDIFF(now(), EditDate) < 90";
                else
                    strdays = "";

                string strRemoveDuplicate = " AND (DupwithContact IS NULL OR DupwithContact='')";
                string strDefaultSort = " Order By ContactID Desc";
                string query = " SELECT ContactID,trim(concat(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName))) as ClientName,DateBirth,State,udf_getformatedDate(CreateDate) as CreateDate ,udf_getformatedDate(EditDate) AS EditDate,(select count(*) from crm_contact Where deletedFlag=0 " + strRemoveDuplicate;
                if (!string.IsNullOrEmpty(searchtext))
                {
                    String[] Words = searchtext.Split(' ');
                    if (Words.Length == 2)
                    {
                        var sbWhere = new StringBuilder(" AND ")
                            .AppendFormat("(FirstName like '{0}%'", Words[0])
                            .AppendFormat(" AND SurName like '{0}%')", Words[1])
                            .Append(conditiontext);
                        var sbSql = new StringBuilder(query);
                        sbSql.Append(sbWhere + strdays);
                        sbSql.AppendFormat(") as Total from crm_contact WHERE deletedFlag=0 {0}", strRemoveDuplicate);
                        sbSql.Append(sbWhere + strdays);
                        if (string.IsNullOrEmpty(sortingText))
                            sbSql.Append(strDefaultSort);
                        else
                            sbSql.AppendFormat(" order by {0}", sortingText);

                        sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(sbSql.ToString());
                    }
                    else if (Words.Length == 3 && !IsNumberOnly(searchtext))
                    {
                        var sbWhere = new StringBuilder(" AND ")
                           .AppendFormat("(FirstName like '{0}%'", Words[0])
                           .AppendFormat(" AND MiddleName like '{0}%'", Words[1])
                           .AppendFormat(" AND SurName like '{0}%')", Words[2])
                           .Append(conditiontext);
                        var sbSql = new StringBuilder(query);
                        sbSql.Append(sbWhere + strdays);
                        sbSql.AppendFormat(") as Total from crm_contact WHERE deletedFlag=0 {0}", strRemoveDuplicate);
                        sbSql.Append(sbWhere + strdays);
                        if (string.IsNullOrEmpty(sortingText))
                            sbSql.Append(strDefaultSort);
                        else
                            sbSql.AppendFormat(" order by ", sortingText);

                        sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(sbSql.ToString());

                    }
                    else
                    {
                        var sbWhere = new StringBuilder(" AND ");
                        if (IsEmailOnly(searchtext))
                            sbWhere.AppendFormat("(Email like '{0}%' OR secondEmailAddress like '{0}%' )", searchtext);
                        else if (IsNumberOnly(searchtext))
                        {
                            searchtext = searchtext.Replace(" ", "");
                            string strmob = string.Empty;

                            int len = searchtext.Length;

                            if (searchtext.Contains(" "))
                                sbWhere.AppendFormat("(Mobile like '{0}%')", searchtext);
                            else
                            {
                                if (len == 10)
                                    strmob = searchtext.Substring(0, 4) + " " + searchtext.Substring(4, 3) + " " + searchtext.Substring(7, 3);
                                else if (len > 4 && len <= 7)
                                    strmob = searchtext.Substring(0, 4) + " " + searchtext.Substring(4, len - 4);
                                else if (len > 7 && len < 10)
                                    strmob = searchtext.Substring(0, 4) + " " + searchtext.Substring(4, len - 4) + " " + searchtext.Substring(7, len - 7);
                                else if (len <= 4 || len > 10)
                                    strmob = searchtext;

                                sbWhere.AppendFormat(" (Mobile like '{0}%')", strmob);
                            }
                        }
                        else
                            sbWhere.AppendFormat("(SurName like '{0}%')", searchtext);
                        sbWhere.Append(conditiontext);
                        var sbSql = new StringBuilder(query);
                        sbSql.Append(sbWhere + strdays);

                        sbSql.AppendFormat(") as Total from crm_contact WHERE deletedFlag=0 {0}", strRemoveDuplicate);
                        sbSql.Append(sbWhere + strdays);
                        if (string.IsNullOrEmpty(sortingText))
                            sbSql.Append(strDefaultSort);
                        else
                            sbSql.AppendFormat(" order by {0}", sortingText);
                        sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(sbSql.ToString());
                    }
                }
                else
                {
                    if (!string.IsNullOrEmpty(conditiontext))
                    {
                        var sbSql = new StringBuilder(query)
                        .Append(conditiontext + strdays)
                        .AppendFormat(" ) as Total from crm_contact WHERE deletedFlag=0 {0}", strRemoveDuplicate)
                        .Append(conditiontext + strdays);

                        if (string.IsNullOrEmpty(sortingText))
                            sbSql.Append(strDefaultSort);
                        else
                            sbSql.AppendFormat(" order by {0}", sortingText);
                        sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(sbSql.ToString());
                    }
                    else
                    {
                        var sbSql = new StringBuilder(query + strdays)
                        .AppendFormat(" ) as Total from crm_contact WHERE deletedFlag=0 {0}{1}", strRemoveDuplicate, strdays);
                        if (string.IsNullOrEmpty(sortingText))
                            sbSql.Append(strDefaultSort);
                        else
                            sbSql.AppendFormat(" order by {0}", sortingText);

                        sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(sbSql.ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet GetApplicationContact(int ContactID,string OperatorId)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID", ContactID),
                    DBhelper.SetParameter("@OperatorID", OperatorId),
                    DBhelper.SetParameter("@LatestTime", DateTime.Now),
                };

                string sbSql = "insert into crm_Recent(ContactID,OperatorID,LatestTime) values(@ContactID,@OperatorID,@LatestTime); selECT *,IFNULL(DupwithContact,0) AS dupwith, udf_getformatedDate(createDate) as cdate FROM crm_contact where contactid=@ContactID;";
                ds = DBhelper.ExecuteDataset(sbSql, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet GetAppLoanRecord(int ContactID, string sortText)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID", ContactID),
                    DBhelper.SetParameter("@sortText", sortText),
                };

                StringBuilder query = new StringBuilder("SELECT a.PurposeSummary, a.app_id,udf_getformatedDate(a.CreatedDate) AS cdate,a.CreatedDate AS 'age',udf_getformatedDate(a.EditDate) AS EditDate, a.CreatedDate as cdate1, a.EditDate as EditDate1,")
                             .Append(" CAST(a.DesiredAmount AS DECIMAL) AS DesiredAmount, CAST(a.OfferedAmount AS DECIMAL) AS OfferedAmount ,a.app_result,")
                             .Append(" (SELECT CONCAT(FirstName,' ',MiddleName,' ',SurName) AS ClientName FROM crm_contact WHERE ContactID=@ContactID)AS ClientName")
                             .Append(" FROM crm_application a INNER JOIN ( SELECT ac.app_id FROM crm_applicationcontact ac INNER JOIN `crm_contact` c ON c.`ContactID`= ac.`ContactID`")
                             .Append(" WHERE c.ContactID=@ContactID OR c.DupwithContact=@ContactID) tblAC ON tblAC.app_id=a.app_id AND a.deletedFlag=0 ORDER BY @sortText");
                ds = DBhelper.ExecuteDataset(query.ToString(), para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        public static DataSet GetApplicationForClient(int contactID)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID", contactID),
                };

                string query = "Select crm_applicationcontact.app_id from crm_applicationcontact inner join crm_contact c on c.ContactID=crm_applicationcontact.ContactID where crm_applicationcontact.deletedFlag=0 AND (c.ContactID=@ContactID OR c.DupwithContact=@ContactID)";
                ds = DBhelper.ExecuteDataset(query, para);
            }
            catch (Exception EX)
            {
                throw EX;
            }
            return ds;
        }
        public static bool CheckContactID(int ContactID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID", ContactID),
                };

                string query = "Select * from crm_contact where ContactID=@ContactID AND deletedFlag=0";
                DataSet ds = DBhelper.ExecuteDataset(query, para);

                if (ds.Tables[0].Rows.Count > 0)
                    flag = true;
            }
            catch (Exception EX)
            {
                throw EX;
            }
            return flag;
        }

        public static bool CheckForDuplicate(string FirstName, string SurName, string DateBirth, string EMail,string Email2, string Mobile, string Street, string State, string Suburb)
        {
            bool flag = false;
            try
            {
                string[] strdate = DateBirth.Split('/');
                DateBirth = strdate[2] + "-" + strdate[1] + "-" + strdate[0];
                var para = new[] {
                    DBhelper.SetParameter("@FirstName", FirstName),
                    DBhelper.SetParameter("@SurName", SurName),
                    DBhelper.SetParameter("@DateBirth", DateBirth),
                    DBhelper.SetParameter("@EMail", EMail),
                    DBhelper.SetParameter("@EMail2", Email2),
                    DBhelper.SetParameter("@Mobile", Mobile),
                    DBhelper.SetParameter("@Street", Street),
                    DBhelper.SetParameter("@State", State),
                    DBhelper.SetParameter("@Suburb", Suburb),
                };

                string query = "Select count(*) as avail from crm_contact where deletedFlag=0 AND DupwithContact IS NULL AND ((FirstName=@FirstName AND SurName=@SurName AND DateBirth=@DateBirth)  OR ( Email=@EMail AND Email IS NOT NULL AND Email <>'' ) OR ( secondEmailAddress=@EMail2 AND secondEmailAddress IS NOT NULL AND secondEmailAddress <>'' )  OR ( Mobile=@Mobile AND Mobile IS NOT NULL AND Mobile <>'' ) OR (Street=@Street AND State=@State AND Suburb=@Suburb))";
                DataSet ds = DBhelper.ExecuteDataset(query, para);

                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    if (Convert.ToInt32(ds.Tables[0].Rows[0]["avail"].ToString()) > 1)
                        flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
        public static DataSet GetApplicationContactForm(int ContactID)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID", ContactID),
                };

                string query = "Select info.app_id,udf_getformatedDate(CONCAT(info.app_Date,' ',info.app_time)) as cdate,info.app_Date as 'app_Date1',info.app_LoanAmount,info.app_ApplyWithSomeoneWhoOwns as assets,CONCAT(c. FirstName,' ',c.MiddleName,' ',c.SurName) as ClientName From app_info info inner join crm_contact c on c.app_id=info.app_id Where c.deletedFlag=0 AND (c.ContactID=@ContactID OR c.DupwithContact=@ContactID)";
                ds = DBhelper.ExecuteDataset(query, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet AddApplicationContact(string Title, string FirstName, string MiddleName, string SurName, string Citizenship, string DateBirth, string DriverLicense,
            string Mobile, string HomePhone, string Email, string secondEmailAddress, string StreetNo, string Street, string Suburb, string State, string PostCode, string app_ResidentialStatus,
            string ResidencePeriod, string ResidenceLength, string unsubscribe_Ad, string Dependant, string Memo, string history_op)
        {
            DataSet ds;
            try
            {
                DateTime dtDateBirth = new DateTime();
                DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
                dtfi.ShortDatePattern = "yyyy-MM-dd";
                dtfi.DateSeparator = "-";
                if (DateBirth != "")
                    dtDateBirth = Convert.ToDateTime(DateBirth, dtfi);

                var para = new[] {
                    DBhelper.SetParameter("p_Title", Title),
                    DBhelper.SetParameter("p_FirstName", FirstName),
                    DBhelper.SetParameter("p_MiddleName", MiddleName),
                    DBhelper.SetParameter("p_SurName", SurName),
                    DBhelper.SetParameter("p_Citizenship", Citizenship),
                    DBhelper.SetParameter("p_DateBirth", dtDateBirth),
                    DBhelper.SetParameter("p_DriverLicense", DriverLicense),
                    DBhelper.SetParameter("p_Mobile", Mobile),
                    DBhelper.SetParameter("p_HomePhone", HomePhone),
                    DBhelper.SetParameter("p_Email", Email),
                    DBhelper.SetParameter("p_secondEmailAddress", secondEmailAddress),
                    DBhelper.SetParameter("p_Street", Street),
                    DBhelper.SetParameter("p_StreetNo", StreetNo),
                    DBhelper.SetParameter("p_Suburb", Suburb),
                    DBhelper.SetParameter("p_State", State),
                    DBhelper.SetParameter("p_PostCode", PostCode),
                    DBhelper.SetParameter("p_app_ResidentialStatus", app_ResidentialStatus),
                    DBhelper.SetParameter("p_ResidencePeriod", ResidencePeriod),
                    DBhelper.SetParameter("p_unsubscribe_Ad", unsubscribe_Ad),
                     DBhelper.SetParameter("p_ResidenceLength", ResidenceLength),
                    DBhelper.SetParameter("p_Dependant", Dependant),
                    DBhelper.SetParameter("p_Memo", Memo),
                    DBhelper.SetParameter("p_historyOperator",history_op)
                };

                ds = DBhelper.ExecuteDatasetSP("sp_addContactHistory", para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static int EditApplicationContact(int ContactID, string Title, string FirstName, string MiddleName, string SurName, string Citizenship, string DateBirth, string DriverLicense,
            string Mobile, string HomePhone, string Email, string secondEmailAddress, string StreetNo, string Street, string Suburb, string State, string PostCode, string app_ResidentialStatus, string ResidencePeriod, string ResidenceLength,string unsubscribe_Ad, string Dependant, string Memo, string history_op)
        {
            int intReturnValue = 0;
            try
            {
                DateTime dtDateBirth = new DateTime();
                DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
                dtfi.ShortDatePattern = "yyyy-MM-dd";
                dtfi.DateSeparator = "-";
                if (DateBirth != "")
                    dtDateBirth = Convert.ToDateTime(DateBirth, dtfi);

                var para = new[] {
                    DBhelper.SetParameter("p_ContactID", ContactID),
                    DBhelper.SetParameter("p_Title", Title),
                    DBhelper.SetParameter("p_FirstName", FirstName),
                    DBhelper.SetParameter("p_MiddleName", MiddleName),
                    DBhelper.SetParameter("p_SurName", SurName),
                    DBhelper.SetParameter("p_Citizenship", Citizenship),
                    DBhelper.SetParameter("p_DateBirth", dtDateBirth),
                    DBhelper.SetParameter("p_DriverLicense", DriverLicense),
                    DBhelper.SetParameter("p_Mobile", Mobile),
                    DBhelper.SetParameter("p_HomePhone", HomePhone),
                    DBhelper.SetParameter("p_Email", Email),
                    DBhelper.SetParameter("p_secondEmailAddress", secondEmailAddress),
                    DBhelper.SetParameter("p_Street", Street),
                    DBhelper.SetParameter("p_StreetNo", StreetNo),
                    DBhelper.SetParameter("p_Suburb", Suburb),
                    DBhelper.SetParameter("p_State", State),
                    DBhelper.SetParameter("p_PostCode", PostCode),
                    DBhelper.SetParameter("p_app_ResidentialStatus", app_ResidentialStatus),
                    DBhelper.SetParameter("p_ResidencePeriod", ResidencePeriod),
                    DBhelper.SetParameter("p_ResidenceLength", ResidenceLength),
                    DBhelper.SetParameter("p_unsubscribe_Ad", unsubscribe_Ad),
                    DBhelper.SetParameter("p_Dependant", Dependant),
                    DBhelper.SetParameter("p_Memo", Memo),
                    DBhelper.SetParameter("p_historyOperator",history_op)
                };

                DBhelper.ExecuteNonQuerySP("sp_updateContactHistory", para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int PartiesContactAdd(int app_id, string Title, string FirstName, string MiddleName, string SurName, string Role, string Relationship, string DateBirth, string MobilePhone, string HomePhone, string WorkPhone, string FaxNumber, string Email1, string Email2, string Street, string Suburb, string State, string PostCode, string PostalAddress, string IncomeType, string TypeofEntity, string BusinessName, string ABN, string NatureofBusiness, string Phone, string Address, string Employer, string Occupation, string JobPosition, string EmployerPhone, string ContactPerson, string WorkAddress, string KinsName, string KinsRelationship, string ContactNumber, string Notes)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("p_AppID", app_id),
                    DBhelper.SetParameter("p_Title", Title),
                    DBhelper.SetParameter("p_FirstName", FirstName),
                    DBhelper.SetParameter("p_MiddleName", MiddleName),
                    DBhelper.SetParameter("p_SurName", SurName),
                    DBhelper.SetParameter("p_Role", Role),
                    DBhelper.SetParameter("p_Relationship", Relationship),
                    DBhelper.SetParameter("p_DateBirth", DateBirth),
                    DBhelper.SetParameter("p_MobilePhone", MobilePhone),
                    DBhelper.SetParameter("p_HomePhone", HomePhone),
                    DBhelper.SetParameter("p_WorkPhone", WorkPhone),
                    DBhelper.SetParameter("p_FaxNumber", FaxNumber),
                    DBhelper.SetParameter("p_Email1", Email1),
                    DBhelper.SetParameter("p_Email2", Email2),
                    DBhelper.SetParameter("p_Street", Street),
                    DBhelper.SetParameter("p_Suburb", Suburb),
                    DBhelper.SetParameter("p_State", State),
                    DBhelper.SetParameter("p_PostCode", PostCode),
                    DBhelper.SetParameter("p_PostalAddress", PostalAddress),
                    DBhelper.SetParameter("p_IncomeType", IncomeType),
                    DBhelper.SetParameter("p_TypeofEntity", TypeofEntity),
                    DBhelper.SetParameter("p_BusinessName", BusinessName),
                    DBhelper.SetParameter("p_ABN", ABN),
                    DBhelper.SetParameter("p_NatureofBusiness", NatureofBusiness),
                    DBhelper.SetParameter("p_Phone", Phone),
                    DBhelper.SetParameter("p_Address", Address),
                    DBhelper.SetParameter("p_Employer", Employer),
                    DBhelper.SetParameter("p_Occupation", Occupation),
                    DBhelper.SetParameter("p_JobPosition", JobPosition),
                    DBhelper.SetParameter("p_EmployerPhone", EmployerPhone),
                    DBhelper.SetParameter("p_ContactPerson", ContactPerson),
                    DBhelper.SetParameter("p_WorkAddress", WorkAddress),
                    DBhelper.SetParameter("p_KinsName", KinsName),
                    DBhelper.SetParameter("p_KinsRelationship", KinsRelationship),
                    DBhelper.SetParameter("p_ContactNumber", ContactNumber),
                    DBhelper.SetParameter("p_Notes", Notes)
                };

                DBhelper.ExecuteNonQuerySP("sp_addContactPartiesHistory", para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int PartiesContactEdit(int ContactID, string Title, string FirstName, string MiddleName, string SurName, string Role, string Relationship, string DateBirth, string MobilePhone, string HomePhone, string WorkPhone, string FaxNumber, string Email1, string Email2, string Street, string Suburb, string State, string PostCode, string PostalAddress, string IncomeType, string TypeofEntity, string BusinessName, string ABN, string NatureofBusiness, string Phone, string Address, string Employer, string Occupation, string JobPosition, string EmployerPhone, string ContactPerson, string WorkAddress, string KinsName, string KinsRelationship, string ContactNumber, string Notes)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_ContactID", ContactID),
                    DBhelper.SetParameter("@p_Title", Title),
                    DBhelper.SetParameter("@p_FirstName", FirstName),
                    DBhelper.SetParameter("@p_MiddleName", MiddleName),
                    DBhelper.SetParameter("@p_SurName", SurName),
                    DBhelper.SetParameter("@p_Role", Role),
                    DBhelper.SetParameter("@p_Relationship", Relationship),
                    DBhelper.SetParameter("@p_DateBirth", DateBirth),
                    DBhelper.SetParameter("@p_MobilePhone", MobilePhone),
                    DBhelper.SetParameter("@p_HomePhone", HomePhone),
                    DBhelper.SetParameter("@p_WorkPhone", WorkPhone),
                    DBhelper.SetParameter("@p_FaxNumber", FaxNumber),
                    DBhelper.SetParameter("@p_Email1", Email1),
                    DBhelper.SetParameter("@p_Email2", Email2),
                    DBhelper.SetParameter("@p_Street", Street),
                    DBhelper.SetParameter("@p_Suburb", Suburb),
                    DBhelper.SetParameter("@p_State", State),
                    DBhelper.SetParameter("@p_PostCode", PostCode),
                    DBhelper.SetParameter("@p_PostalAddress", PostalAddress),
                    DBhelper.SetParameter("@p_IncomeType", IncomeType),
                    DBhelper.SetParameter("@p_TypeofEntity", TypeofEntity),
                    DBhelper.SetParameter("@p_BusinessName", BusinessName),
                    DBhelper.SetParameter("@p_ABN", ABN),
                    DBhelper.SetParameter("@p_NatureofBusiness", NatureofBusiness),
                    DBhelper.SetParameter("@p_Phone", Phone),
                    DBhelper.SetParameter("@p_Address", Address),
                    DBhelper.SetParameter("@p_Employer", Employer),
                    DBhelper.SetParameter("@p_Occupation", Occupation),
                    DBhelper.SetParameter("@p_JobPosition", JobPosition),
                    DBhelper.SetParameter("@p_EmployerPhone", EmployerPhone),
                    DBhelper.SetParameter("@p_ContactPerson", ContactPerson),
                    DBhelper.SetParameter("@p_WorkAddress", WorkAddress),
                    DBhelper.SetParameter("@p_KinsName", KinsName),
                    DBhelper.SetParameter("@p_KinsRelationship", KinsRelationship),
                    DBhelper.SetParameter("@p_ContactNumber", ContactNumber),
                    DBhelper.SetParameter("@p_Notes", Notes)
                };

                DBhelper.ExecuteNonQuerySP("sp_updateContactPartiesHistory", para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static bool RemoveDuplicateContact(int CurrentContactID, int DupwithContactID)
        {
            bool flag = false;
            try
            {
                var para = new[]
                {
                    DBhelper.SetParameter("@ContactID",CurrentContactID),
                    DBhelper.SetParameter("@DupwithContact",DupwithContactID),
                };

                string query = "Update crm_contact SET DupwithContact=@DupwithContact WHERE ContactID=@ContactID;Update crm_applicationcontact SET ContactID=@DupwithContact where ContactID=@ContactID; " +
                               "Update crm_vedacreditcheckhistory SET CurrentContactID=@DupwithContact where CurrentContactID=@ContactID;";
                DBhelper.ExecuteNonQuery(query, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool deleteClient(int ContactID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID",ContactID)
                };

                string query = "Update crm_contact SET deletedFlag=1 WHERE ContactID=@ContactID; Update crm_applicationcontact SET deletedFlag=1 WHERE ContactID=@ContactID";
                DBhelper.ExecuteNonQuery(query, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static int DeleteParties(int ContactID)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_ContactID", ContactID),
                };

                string sqlUpdateContactQuery = "delete from crm_contact where ContactID=@p_ContactID";
                DBhelper.ExecuteNonQuery(sqlUpdateContactQuery, para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int GetTotalParties(int LoanID)
        {
            int count = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@LoanID", LoanID),
                };

                string query = "select count(*) as totalparties from crm_contact where app_id=(select app_id from am_loan1 where LoanID=@LoanID)";
                DataSet ds = DBhelper.ExecuteDataset(query, para);

                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    count = Convert.ToInt32(ds.Tables[0].Rows[0]["totalparties"].ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return count;
        }

        private static bool IsNumberOnly(string value)
        {
            string validChar = "0123456789 ";
            foreach (char c in value)
            {
                if (!validChar.Contains(c.ToString()))
                    return false;
            }
            return true;
        }

        private static bool IsEmailOnly(string value)
        {
            var regex = new Regex(@"[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?");
            return regex.IsMatch(value);
        }
    }
}
